package DBLag;

import ModelLag.Customer;
import ModelLag.Employee;
import ModelLag.Event;
import ModelLag.OrderArr;
import ModelLag.Room;
import java.util.ArrayList;
import java.sql.*;
import java.util.GregorianCalendar;

/**
 * @author DM75 Gruppe 3
 * @version 2011-12-22
 */
public class DbOrderArr implements IFDbOrderArr{

    private Connection con;
    private static final String DB_NAME = "OrderArr";

    public DbOrderArr() {
        this.con = DbConnection1.getInstance().getDBcon();
    }
 @Override
        public int getNextOrderID()
    {
        
        String query = null;
        ResultSet results = null;
        int nextInt =0;
        try{
            query = "select MAX(orderID) from " + DB_NAME;
            PreparedStatement preStmt = con.prepareStatement(query);
            preStmt.setQueryTimeout(5);            
            results = preStmt.executeQuery();   
        if(results.next()){
            nextInt = results.getInt(1);
            nextInt++;
            //System.out.println("NEXTINT ===== " + nextInt);
        }
        preStmt.close();
        return nextInt; 
        
        }
        catch(SQLTimeoutException timeout)
        {
            System.out.println(timeout.getMessage());
        return -1;
        }catch(Exception e)
        {
            System.out.println(e.getMessage());
        return -1;
        }            
    }
    
    @Override
    public OrderArr findOrderArr(int orderID) {
        PreparedStatement preStmnt = null;    
        String query = "SELECT * FROM "+DB_NAME+" WHERE orderID = ?";
        OrderArr orderObj = null;
        ResultSet results;
        try{    
            preStmnt = con.prepareStatement(query);
            preStmnt.setQueryTimeout(5);            
            preStmnt.setInt(1,orderID);                
            results = preStmnt.executeQuery();
            
            if(results.next()){
                 orderObj = buildOrderArr(results);	
        }
        preStmnt.close();    
        }catch(SQLTimeoutException timeout)
        {
        System.out.println(timeout.getMessage());
        }
        catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
        
            catch(Exception e){
                System.out.println("Fejl i finderSpecificOrderArr(): " + e.getMessage());               
            }         
            return orderObj;
    }

    @Override
    public ArrayList<OrderArr> searchOrderArr(String searchString) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    @Override
    public ArrayList<OrderArr> getEverythingOrderArr(boolean isPeriod, String from, String to)
    {
        PreparedStatement preStmnt = null;      
        ArrayList<OrderArr> orderList = new ArrayList<OrderArr>();
        ResultSet results;
        OrderArr orderObj = null;
        String query=null;
        
        try{
        if(!isPeriod)
        {
            query = "SELECT orderID,date,startTime,endTime,amountOfLanes,noOfPersons,noOfPersonsOnlyFood,totalPrice,Customer.phoneNo, Customer.fName, Customer.lName, Customer.address, Customer.email, Room.roomName, Room.size, Employee.cprNo AS eCprNo,Employee.fName AS eFname, Employee.lName AS eLname,Employee.address AS eAddress, Employee.email AS eEmail, Employee.phoneNo AS ePhoneNo,Event.eventName, Event.pricePrPerson, Event.pricePrPersonOnlyFood, description, bowlingFrom FROM OrderArr,Customer,Employee, Room, Event WHERE OrderArr.phoneNo = Customer.phoneNo AND OrderArr.cprNo = Employee.cprNo AND OrderArr.eventName = Event.eventName AND OrderArr.roomName = Room.roomName ORDER BY date ASC";
        }else{
            query = "SELECT orderID,date,startTime,endTime,amountOfLanes,noOfPersons,noOfPersonsOnlyFood,totalPrice,Customer.phoneNo, Customer.fName, Customer.lName, Customer.address, Customer.email, Room.roomName, Room.size, Employee.cprNo AS eCprNo,Employee.fName AS eFname, Employee.lName AS eLname,Employee.address AS eAddress, Employee.email AS eEmail, Employee.phoneNo AS ePhoneNo,Event.eventName, Event.pricePrPerson, Event.pricePrPersonOnlyFood, description, bowlingFrom FROM OrderArr,Customer,Employee, Room, Event WHERE date BETWEEN '" + from + "' AND '" +to + "' AND OrderArr.phoneNo = Customer.phoneNo AND OrderArr.cprNo = Employee.cprNo AND OrderArr.eventName = Event.eventName AND OrderArr.roomName = Room.roomName ORDER BY date ASC";           
        }   
            preStmnt = con.prepareStatement(query);
            preStmnt.setQueryTimeout(5);            
            results = preStmnt.executeQuery();
            while(results.next()){
                 orderObj = buildEverythingOrderArr(results);
                 orderList.add(orderObj);	
            }
            preStmnt.close();
            }catch(SQLTimeoutException timeout)
        {
            System.out.println(timeout.getMessage());
        }
        catch(SQLException SQLe)
            {
                System.out.println("SQL Exception in bigorder: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i geteverythingOrderArr(): " + e.getMessage());    
            }
            
            return orderList;                
    }
    @Override
    public ArrayList<OrderArr> getAllOrderArr() {
        PreparedStatement preStmnt = null;          
        ArrayList<OrderArr> orderList = new ArrayList<OrderArr>();
        ResultSet results;
        OrderArr orderObj = null;
        try{
            String query = "SELECT * FROM " + DB_NAME + " ORDER BY date ASC";     
            preStmnt = con.prepareStatement(query);
            preStmnt.setQueryTimeout(5);            
            results = preStmnt.executeQuery();
            while(results.next()){
                 orderObj = buildOrderArr(results);
                 orderList.add(orderObj);	
            }
            preStmnt.close();
        }catch(SQLTimeoutException timeout)
        {
        System.out.println(timeout.getMessage());
        }
        catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findAllOrderArr(): " + e.getMessage());            
            }           
            return orderList;
    }

    @Override   
    public int insertOrderArr(OrderArr order){
        int rc = -1;
        PreparedStatement preStmnt = null;
        String query = null;   
        try
        {
                query = "INSERT INTO " +DB_NAME+ " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
                preStmnt = con.prepareStatement(query);
                preStmnt.setQueryTimeout(5);                
                java.sql.Date tempDate = new java.sql.Date(order.getDate().getTimeInMillis());
                preStmnt.setInt(1, order.getOrderID());
                preStmnt.setDate(2, tempDate);
                preStmnt.setString(3, order.getStartTime());
                preStmnt.setString(4, order.getEndTime());
                preStmnt.setInt(5, order.getAmountOfLanes());
                preStmnt.setInt(6, order.getNoOfPersons());                
                preStmnt.setInt(7, order.getNoOfPersonsOnlyFood());
                preStmnt.setDouble(8, order.getTotalPrice());
                preStmnt.setString(9, order.getCustomer().getPhoneNo());
                preStmnt.setString(10, order.getRoom().getRoomName());
                preStmnt.setString(11, order.getEmployee().getCprNo());
                preStmnt.setString(12, order.getEvent().getEventName());
                preStmnt.setString(13, order.getDescription());  
                preStmnt.setString(14, order.getBowlingFrom());  
                rc = preStmnt.executeUpdate();   
                preStmnt.close();
        }
        catch(SQLTimeoutException timeout)
        {
            System.out.println(timeout.getMessage());
        return -1;
        }catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQLException in InsertOrderArr " + SQLe.getMessage());   
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception in InsertOrderArr" + e.getMessage());
        }        
        return rc;
    }

    @Override
    public int updateOrderArr(OrderArr order) {
        int rc = -1;
        String query = null;     
        java.sql.Date newsqldate = new java.sql.Date(order.getDate().getTimeInMillis());
        try
        {
            query = "UPDATE " +DB_NAME+ " SET date = ?,startTime = ?,endTime = ?,amountOfLanes = ?," 
            + "noOfPersons = ?,noOfPersonsOnlyFood = ?,totalPrice = ?,phoneNo = ?,roomName = ?,cprNo = ?,eventName = ?, "
            +"description = ?, bowlingFrom = ? WHERE orderID = " + order.getOrderID();
            PreparedStatement preStmnt = con.prepareStatement(query);
            preStmnt.setQueryTimeout(5);            
            preStmnt = con.prepareStatement(query);
           
                preStmnt.setDate(1, newsqldate);
                preStmnt.setString(2, order.getStartTime());
                preStmnt.setString(3, order.getEndTime());
                preStmnt.setInt(4, order.getAmountOfLanes());
                preStmnt.setInt(5, order.getNoOfPersons());                
                preStmnt.setInt(6, order.getNoOfPersonsOnlyFood());
                preStmnt.setDouble(7, order.getTotalPrice());
                preStmnt.setString(8, order.getCustomer().getPhoneNo());
                preStmnt.setString(9, order.getRoom().getRoomName());
                preStmnt.setString(10, order.getEmployee().getCprNo());
                preStmnt.setString(11, order.getEvent().getEventName());
                preStmnt.setString(12, order.getDescription());  
                preStmnt.setString(13, order.getBowlingFrom());  
            rc = preStmnt.executeUpdate();
            preStmnt.close();               
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());
        }        
        return rc;
    }

    @Override
    public int deleteOrderArr(OrderArr order) {
        int rc = -1;
        PreparedStatement preStmnt = null;
        String query = null;
        try{
                query = "DELETE FROM " +DB_NAME+ " WHERE orderID = ?";
                preStmnt = con.prepareStatement(query);
                preStmnt.setQueryTimeout(5);
                preStmnt.setInt(1, order.getOrderID());
                rc = preStmnt.executeUpdate();
                preStmnt.close();
        }catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Fejl i deleteOrderErr: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("Exception i deleteOrderErr" + e.getMessage());
        }        
        return rc;
    }

    private OrderArr buildOrderArr(ResultSet results) {
                OrderArr orderArrObj = new OrderArr();
                DbCustomer tempCustomer = new DbCustomer();
                DbEmployee tempEmployee = new DbEmployee();
                DbRoom tempRoom = new DbRoom();
                DbEvent tempEvent = new DbEvent();
                GregorianCalendar newCal =  new GregorianCalendar();       
         try{                       
                orderArrObj.setOrderID(results.getInt("orderID"));               //sets order id in object
                newCal.setTimeInMillis(results.getDate("date").getTime());       //sets the newCal Calendar objects timevalue to the date in the database(in millis)
                orderArrObj.setDate(newCal);                                     //sets the objects calendar field with the newCal object
                orderArrObj.setStartTime(results.getString("startTime"));
                orderArrObj.setEndTime(results.getString("endTime"));
                orderArrObj.setAmountOfLanes(results.getInt("amountOfLanes"));
                orderArrObj.setNoOfPersons(results.getInt("noOfPersons"));
                orderArrObj.setNoOfPersonsOnlyFood(results.getInt("noOfPersonsOnlyFood"));
                orderArrObj.setTotalPrice(results.getDouble("totalPrice"));
                orderArrObj.setCustomer(tempCustomer.findCustomer(results.getString("phoneNo")));
                orderArrObj.setEmployee(tempEmployee.findEmployee(results.getString("cprNo")));
                orderArrObj.setRoom(    tempRoom.findRoom(results.getString("roomName")));
                orderArrObj.setEvent(tempEvent.findEvent(results.getString("eventName")));
                orderArrObj.setDescription(results.getString("description"));
                orderArrObj.setBowlingFrom(results.getString("bowlingFrom"));               
          }
         catch(Exception e)
         {
             System.out.println("error building order object: " + e.getMessage());
         }       
        return orderArrObj;     
    }
    /**
     * 
     * @param results
     * @return
     */
    private OrderArr buildEverythingOrderArr(ResultSet results) {
                OrderArr orderArrObj = new OrderArr();
                Customer newCustomer = new Customer();
                Employee newEmployee = new Employee();
                Room newRoom = new Room();
                Event newEvent = new Event();
                GregorianCalendar newCal =  new GregorianCalendar();    
         try{                      
                orderArrObj.setOrderID(results.getInt("orderID"));               //sets order id in object
                newCal.setTimeInMillis(results.getDate("date").getTime());       //sets the newCal Calendar objects timevalue to the date in the database(in millis)
                orderArrObj.setDate(newCal);                                     //sets the objects calendar field with the newCal object
                orderArrObj.setStartTime(results.getString("startTime"));
                orderArrObj.setEndTime(results.getString("endTime"));
                orderArrObj.setAmountOfLanes(results.getInt("amountOfLanes"));
                orderArrObj.setNoOfPersons(results.getInt("noOfPersons"));
                orderArrObj.setNoOfPersonsOnlyFood(results.getInt("noOfPersonsOnlyFood"));
                orderArrObj.setTotalPrice(results.getDouble("totalPrice"));
                //Creating the customer
                newCustomer.setPhoneNo(results.getString("phoneNo"));
                newCustomer.setfName(results.getString("fName"));
                newCustomer.setlName(results.getString("lName"));
                newCustomer.setAddress(results.getString("address"));
                newCustomer.setEmail(results.getString("email"));
                orderArrObj.setCustomer(newCustomer);
                //Creating the employee
                newEmployee.setCprNo(results.getString("eCprNo"));
                newEmployee.setfName(results.getString("eFname"));
                newEmployee.setlName(results.getString("eLname"));
                newEmployee.setAddress(results.getString("eAddress"));
                newEmployee.setEmail(results.getString("eEmail"));
                newEmployee.setPhoneNo(results.getString("ePhoneNo"));
                orderArrObj.setEmployee(newEmployee);     
                //Creating the Room
                newRoom.setRoomName(results.getString("roomName"));
                newRoom.setSize(results.getString("size"));
                orderArrObj.setRoom(newRoom);
                //Creating the Event
                newEvent.setEventName(results.getString("eventName"));
                newEvent.setPricePrPerson(results.getDouble("pricePrPerson"));
                newEvent.setPricePrPersonOnlyFood(results.getDouble("pricePrPersonOnlyFood"));                
                orderArrObj.setEvent(newEvent);
                orderArrObj.setDescription(results.getString("description"));
                orderArrObj.setBowlingFrom(results.getString("bowlingFrom"));              
          }
         catch(Exception e)
         {
             System.out.println("error building the big order object: " + e.getMessage());
         }     
        return orderArrObj;  
        } 
}
